package com.xb;

import com.xb.Entity.Customers;
import com.xb.util.dbUtil;
import org.junit.Test;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class BlobTest {
    @Test
    public void testInsert(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = dbUtil.getConnection();
            String sql = "INSERT INTO customers(name,email,birth,photo) values(?,?,?,?)";
            ps = connection.prepareStatement(sql);
            //插入blob
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date parse = simpleDateFormat.parse("2020-01-01");
            FileInputStream fileInputStream = new FileInputStream(new File("dog.jpg"));
            ps.setObject(1,"dog");
            ps.setObject(2,"dog@163.com");
            ps.setObject(3,new java.sql.Date(parse.getTime()));
            ps.setBlob(4,fileInputStream);
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbUtil.closeConAndPs(connection,ps);
        }
    }
    @Test
    public void testUpdate(){
        Connection connection = null;
        PreparedStatement ps = null;
        FileInputStream fileInputStream = null;
        try {
            connection = dbUtil.getConnection();
            String sql = "UPDATE  customers SET photo = ? where id = ?";
            ps = connection.prepareStatement(sql);
            //插入blob
            fileInputStream = new FileInputStream(new File("./dog.jpg"));
            ps.setBlob(1,fileInputStream);
            ps.setObject(2,"1");
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbUtil.closeConAndPsAndFile(connection,ps,fileInputStream);
        }
    }
    @Test
    public void testSelect(){
        Connection connection = null;
        PreparedStatement ps = null;
        FileOutputStream fileOutputStream = null;
        InputStream stream = null;
        try {
            connection = dbUtil.getConnection();
            String sql = "SELECT * FROM customers where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,2);
            ResultSet resultSet = ps.executeQuery();
            //获取元数据
            ResultSetMetaData metaData = ps.getMetaData();
            if (resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                java.sql.Date birth =resultSet.getDate(4);
                Customers customers = new Customers(id, name, email, birth);
                System.out.println(customers);
                Blob file = resultSet.getBlob(5);
                stream = file.getBinaryStream();
                byte[] buffer = new byte[1024];
                fileOutputStream = new FileOutputStream("./dog.jpg");
                int len;
                while ((len = stream.read(buffer))!=-1){
                    fileOutputStream.write(buffer,0,len);
                }
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                dbUtil.closeConAndPs(connection,ps);
                fileOutputStream.close();
                stream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
